#! /usr/bin/env python
#coding=utf-8
from uliweb import expose
from sqlalchemy import *
from uliweb.orm import *
from sqlalchemy.sql import select
from sqlalchemy.sql import and_, or_, not_
import datetime
from datetime import date
from uliweb.contrib.auth import require_login

def __begin__():
    from uliweb import function
    return function('require_login')()
    
def get_date():
    from uliweb import request
    from uliweb import settings
    
    begin_Date = request.GET.get('begin_date','')
    end_Date = request.GET.get('end_date','')
    if begin_Date =='':
        begin_Date = settings.PARA.begin_date
    else:
         begin_Date = begin_Date[:10]
    if end_Date =='':
        end_Date = str(date.today())
    else:
        end_Date = end_Date[:10]    
    begin_Date = datetime.datetime.strptime(begin_Date, '%Y-%m-%d')
    end_Date = datetime.datetime.strptime(end_Date, '%Y-%m-%d')
    begin_Date = begin_Date.date()
    end_Date = end_Date.date()
    
    return begin_Date,end_Date



#项目任务书月报表按照开发中心、项目异常维度展示
@expose('/charts/assignment/month/report/stage/graph')
def charts_assignment_month_report_stage_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.month,
                          func.sum(req_model.c.total_num),
                          func.sum(req_model.c.ys_num_zc),
                          func.sum(req_model.c.ys_num_yc),
                          func.sum(req_model.c.cg_num_zc),
                          func.sum(req_model.c.cg_num_yc),
                          func.sum(req_model.c.ss_num_zc),
                          func.sum(req_model.c.ss_num_yc),
                          func.sum(req_model.c.tg_num_zc),
                          func.sum(req_model.c.tg_num_yc),
                          func.sum(req_model.c.yans_num_zc),
                          func.sum(req_model.c.yans_num_yc),
                          func.sum(req_model.c.js_num_zc),
                          func.sum(req_model.c.js_num_yc),
                          func.sum(req_model.c.wc_num_zc),
                          req_model.c.month_date,
                          ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.type == '1',
                                req_model.c.kfzx == user_zx)
                         ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                                     
      else:        
          sql = select([req_model.c.month,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        req_model.c.month_date,
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.type == '1')
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                   
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      #line_flag = 0
      for row in sql_rs:
         row = list(row)
         #line_flag = 1
         data.append(row)
      response.template = 'charts_assignment_month_report_stage_graph.xml'
      response.content_type = 'text/xml'
      #data['begin_date'] = begin_Date
      #data['end_date'] = end_Date
      print data
      return {'data':data}

#项目任务书月报表按照开发中心、项目异常维度展示
@expose('/charts/assignment/quarter/report/stage/graph')
def charts_assignment_quarter_report_stage_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.quarter,
                          func.sum(req_model.c.total_num),
                          func.sum(req_model.c.ys_num_zc),
                          func.sum(req_model.c.ys_num_yc),
                          func.sum(req_model.c.cg_num_zc),
                          func.sum(req_model.c.cg_num_yc),
                          func.sum(req_model.c.ss_num_zc),
                          func.sum(req_model.c.ss_num_yc),
                          func.sum(req_model.c.tg_num_zc),
                          func.sum(req_model.c.tg_num_yc),
                          func.sum(req_model.c.yans_num_zc),
                          func.sum(req_model.c.yans_num_yc),
                          func.sum(req_model.c.js_num_zc),
                          func.sum(req_model.c.js_num_yc),
                          func.sum(req_model.c.wc_num_zc),
                          req_model.c.month_date,
                          ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.type == '1',
                                req_model.c.kfzx == user_zx)
                         ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                 
            
      else:
          sql = select([req_model.c.quarter,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        req_model.c.month_date,
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.type == '1')
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      #line_flag = 0
      for row in sql_rs:
         row = list(row)
         #line_flag = 1
         data.append(row)
      response.template = 'charts_assignment_month_report_stage_graph.xml'
      response.content_type = 'text/xml'
      #data['begin_date'] = begin_Date
      #data['end_date'] = end_Date
      print data
      return {'data':data}

#准开发需求月报表按照条线维度展示
@expose('/charts/requirement/month/report/tx/graph')
def charts_requirement_month_report_tx_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_tx')               
      data = []
  #    header = []
      sql = select([req_model.c.month_date,
                    req_model.c.month,     
                    req_model.c.total_num, 
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    req_model.c.new_num_09,
                    req_model.c.old_num_09,
                    req_model.c.new_num_10,
                    req_model.c.old_num_10,                    
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date))

      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      response.template = 'charts_requirement_month_report_tx_graph.xml'
      response.content_type = 'text/xml'
      #data['begin_date'] = begin_Date
      #data['end_date'] = end_Date
      print data
      return {'data':data}
      
#季度报表按照条线维度展示
@expose('/charts/requirement/quarter/report/tx/graph')
def charts_requirement_quarter_report_tx_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_quarter_report_tx')               
      data = []
  #    header = []
      sql = select([req_model.c.month_date,
                    req_model.c.quarter,     
                    req_model.c.total_num, 
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    req_model.c.new_num_09,
                    req_model.c.old_num_09,
                    req_model.c.new_num_10,
                    req_model.c.old_num_10,                    
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date))
  
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data.append(row)
      response.template = 'charts_requirement_month_report_tx_graph.xml'
      response.content_type = 'text/xml'
#      result = {}
#      result['name'] = '准开发需求季度报表'
#      result['data'] = data
      
      return {'data':data}

#月报表按照开发中心维度展示
@expose('/charts/requirement/month/report/kfzx/graph')
def charts_requirement_month_report_kfzx_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_kfzx')               
      data1 = []
  #    header = []
      sql = select([req_model.c.month_date,
                    req_model.c.month,
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                   ).order_by(desc(req_model.c.month_date))

      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data1.append(row)
      ######
      total = []
      
      sql_rs = sql.execute()
      for row in sql_rs:
        month_date,month,new_num_01,old_num_01,new_num_02,old_num_02,new_num_03,old_num_03,new_num_04,old_num_04,new_num_05,old_num_05,new_num_06,old_num_06,new_num_07,old_num_07,new_num_08,old_num_08 = row
        x = []
        x.append(month_date)
        x.append(month)
        y = new_num_01+old_num_01+new_num_02+old_num_02+new_num_03+old_num_03+new_num_04+old_num_04+new_num_05+old_num_05+new_num_06+old_num_06+new_num_07+old_num_07+new_num_08+old_num_08
        x.append(y)
        total.append(x)
      ######
 #     response.template = 'charts_requirement_month_report_kfzx_graph.xml'
 #     response.content_type = 'text/xml'
      data = {}
      data['name'] = u'准开发需求中心维度月度报表'
      data['data'] = data1
      data['total'] = total
      #result['label'] = ['北京开发中心新建','北京开发中心续建','上海开发中心新建','上海开发中心续建','厦门开发中心新建','厦门开发中心续建','广州开发中心新建','广州开发中心续建','成都开发中心新建','成都开发中心续建','武汉开发中心新建','武汉开发中心续建','深圳个贷支持中心新建','深圳个贷支持中心续建','香港支持中心新建','香港支持支持中心续建']
      data['label'] = [[u'北京开发中心新建'],[u'北京开发中心续建'],[u'上海开发中心新建'],[u'上海开发中心续建'],[u'厦门开发中心新建'],[u'厦门开发中心续建'],[u'广州开发中心新建'],[u'广州开发中心续建'],[u'成都开发中心新建'],[u'成都开发中心续建'],[u'武汉开发中心新建'],[u'武汉开发中心续建'],[u'深圳个贷支持中心新建'],[u'深圳个贷支持中心续建'],[u'香港支持中心新建'],[u'香港支持中心续建']]
      response.template = 'charts_requirement_month_report_kfzx_graph.xml'
      response.content_type = 'text/xml'
      
      result = {}    
      result['data'] = data      
      return result

#季度报表按照开发中心维度展示
@expose('/charts/requirement/quarter/report/kfzx/graph')
def charts_requirement_quarter_report_kfzx_graph():
    from uliweb.orm import get_model
#    from uliweb.utils.generic import ListView
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
#    from views import *
    
    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    req_model = get_model('requirement_quarter_report_kfzx')               
    data1 = []
#    header = []
    sql = select([req_model.c.month_date,
                    req_model.c.quarter,
                    req_model.c.new_num_01,
                    req_model.c.old_num_01,
                    req_model.c.new_num_02,
                    req_model.c.old_num_02,
                    req_model.c.new_num_03,
                    req_model.c.old_num_03,
                    req_model.c.new_num_04,
                    req_model.c.old_num_04,
                    req_model.c.new_num_05,
                    req_model.c.old_num_05,
                    req_model.c.new_num_06,
                    req_model.c.old_num_06,
                    req_model.c.new_num_07,
                    req_model.c.old_num_07,
                    req_model.c.new_num_08,
                    req_model.c.old_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                    ).order_by(desc(req_model.c.month_date))

    sql_rs = sql.execute()
    ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
    line_flag = 0
    
    for row in sql_rs:
        row = list(row)
        line_flag = 1
        data1.append(row)
    ######
    total = []
    
    sql_rs = sql.execute()
    for row in sql_rs:
        month_date,month,new_num_01,old_num_01,new_num_02,old_num_02,new_num_03,old_num_03,new_num_04,old_num_04,new_num_05,old_num_05,new_num_06,old_num_06,new_num_07,old_num_07,new_num_08,old_num_08 = row
        x = []
        x.append(month_date)
        x.append(month)
        y = new_num_01+old_num_01+new_num_02+old_num_02+new_num_03+old_num_03+new_num_04+old_num_04+new_num_05+old_num_05+new_num_06+old_num_06+new_num_07+old_num_07+new_num_08+old_num_08
        x.append(y)
        total.append(x)
    ######
#     response.template = 'charts_requirement_month_report_kfzx_graph.xml'
#     response.content_type = 'text/xml'
    data = {}
    data['name'] = u'准开发需求中心维度季度报表'
    data['data'] = data1
    data['total'] = total
    #result['label'] = ['北京开发中心新建','北京开发中心续建','上海开发中心新建','上海开发中心续建','厦门开发中心新建','厦门开发中心续建','广州开发中心新建','广州开发中心续建','成都开发中心新建','成都开发中心续建','武汉开发中心新建','武汉开发中心续建','深圳个贷支持中心新建','深圳个贷支持中心续建','香港支持中心新建','香港支持支持中心续建']
    data['label'] = [[u'北京开发中心新建'],[u'北京开发中心续建'],[u'上海开发中心新建'],[u'上海开发中心续建'],[u'厦门开发中心新建'],[u'厦门开发中心续建'],[u'广州开发中心新建'],[u'广州开发中心续建'],[u'成都开发中心新建'],[u'成都开发中心续建'],[u'武汉开发中心新建'],[u'武汉开发中心续建'],[u'深圳个贷支持中心新建'],[u'深圳个贷支持中心续建'],[u'香港支持中心新建'],[u'香港支持中心续建']]
    response.template = 'charts_requirement_month_report_kfzx_graph.xml'
    response.content_type = 'text/xml'
    
    result = {}    
    result['data'] = data      
    return result
    

#月报表按照系统维度展示
@expose('/charts/requirement/month/report/system/graph')
def charts_requirement_month_report_system_graph():
      from uliweb.orm import get_model
      #   from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
      #   from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_system')
      req_sys = get_model('system')               
      data1 = {}
      header = []
    
      #sql_sys为取当前未关闭系统的系统名称，按照sys_id升序排序
      #sql_sys = select([req_sys.c.name,req_sys.c.sys_id],and_(req_sys.c.stage != '4')).order_by(asc(req_sys.c.sys_id))
           
      user_zx = request.user._kfzx_
      if request.user.department=='2':
         sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                                      and_(req_model.c.month_date >= begin_Date,
                                           req_model.c.month_date <= end_Date,
                                           req_model.c.system_id == req_sys.c.sys_id,
                                           req_model.c.kfzx == user_zx)).order_by(asc(req_sys.c.sys_id))      
               
         sql_line = select([func.distinct(req_model.c.month_date),req_model.c.month,],
                     and_( req_model.c.month_date >= begin_Date,
                           req_model.c.month_date <= end_Date,
                           req_model.c.kfzx == user_zx)
                     )

         sql = select([req_model.c.month_date,
                     req_model.c.month,
                     req_model.c.num,            
                     req_model.c.system_id,
                     ],
                     and_( req_model.c.month_date >= begin_Date,
                           req_model.c.month_date <= end_Date,
                           req_model.c.kfzx == user_zx)
                     ).order_by(desc(req_model.c.month_date))
         
      
      else:
           sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                                   and_(req_model.c.month_date >= begin_Date,
                                       req_model.c.month_date <= end_Date,
                                       req_model.c.system_id == req_sys.c.sys_id)).order_by(asc(req_sys.c.sys_id))      
           
           sql_line = select([func.distinct(req_model.c.month_date),req_model.c.month,],
                       and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                       )
           ########################
           sql = select([req_model.c.month_date,
                       req_model.c.month,
                       req_model.c.num,            
                       req_model.c.system_id,
                       ],
                       and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                       ).order_by(desc(req_model.c.month_date))
           
      #初始化header                
      sql_sys_rs = sql_sys.execute()
      #######################################################
      #header = []
      #header.append({'name':'月份'})
      #header.append({'name':'total','verbose_name':'合计', 'width':50})
      for row in sql_sys_rs:
          sys_id,name  = row
          i={}
          i['sys_id']=str(sys_id)
          i['name']=name
          #i['width']=70
          header.append(i)
              
      #print 'header',header
      #######################################################
      
      
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      
      sql_line_rs = sql_line.execute()
      ###################################
      for row in sql_line_rs:
          #print 'row',row
          y,z = row
          y = str(y)
          #print 'y',y,'z',z
          data1[y] = {}
          data1[y]['month'] = z
          data1[y]['total'] = 0
          #print 'data[y]',data
          #print 'header.count()',header
          #print 'len',len(header)
          sql_sys_rs = sql_sys.execute()
          for row in sql_sys_rs:
              sys_id,name  = row
              #print 'h',h
              sys_id = str(sys_id)
              data1[y][sys_id] = 0
              data1[y]['month_date'] = y
      ######################################
      #print 'header',header
      #print 'data',data
      #data_row[]   
      line_flag = 0
      title_num = 0
      ##############
      #total = 0
      ##############
      #header[title_num++] = u'月份’
      for row in sql_rs:
          #print row
          line_flag = 1
          month_date,rr,num,sys_id = row
          sys_id = str(sys_id)
          month_date = str(month_date)
          #print 'month_date',month_date
          #print 'num',num,'data[month_date][name]',data[month_date][name]
          data1[month_date][sys_id] = num
          data1[month_date]['num'] = num
          data1[month_date]['sys_id'] = str(sys_id)
          data1[month_date]['total'] += num
          #print data[month_date][name]
          #print data
      
      #print 'data',data
      data_view = list(sorted(data1.values()))
      

      #初始化总计字典
      data = {}
      data['label'] = header
      data['data'] = data_view
      data['name'] = u'准开发需求系统维度季度报表'
      response.template = 'charts_requirement_month_report_system_graph.xml'
      response.content_type = 'text/xml'
			
      result = {}    
      result['data'] = data      
      return result

#季度报表按照系统维度展示
@expose('/charts/requirement/quarter/report/system/graph')
def charts_requirement_quarter_report_system_graph():
      from uliweb.orm import get_model
      #   from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
      #   from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_quarter_report_system')
      req_sys = get_model('system')               
      data1 = {}
      header = []
      #sql_sys为取当前未关闭系统的系统名称，按照sys_id升序排序
      #sql_sys = select([req_sys.c.name,req_sys.c.sys_id],and_(req_sys.c.stage != '4')).order_by(asc(req_sys.c.sys_id))
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                                   and_(req_model.c.month_date >= begin_Date,
                                        req_model.c.month_date <= end_Date,
                                        req_model.c.system_id == req_sys.c.sys_id,
                                        req_model.c.kfzx == user_zx)).order_by(asc(req_sys.c.sys_id))      
            
            sql_line = select([func.distinct(req_model.c.month_date),req_model.c.quarter,],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.kfzx == user_zx)
                        )
            ########################
            sql = select([req_model.c.month_date,
                        req_model.c.quarter,
                        req_model.c.num,            
                        req_model.c.system_id,
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.kfzx == user_zx)
                        ).order_by(desc(req_model.c.month_date))
                        
      else:
          sql_sys = select([func.distinct(req_sys.c.sys_id),req_sys.c.name],
                                 and_(req_model.c.month_date >= begin_Date,
                                      req_model.c.month_date <= end_Date,
                                      req_model.c.system_id == req_sys.c.sys_id)).order_by(asc(req_sys.c.sys_id))      
          
          sql_line = select([func.distinct(req_model.c.month_date),req_model.c.quarter,],
                      and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                      )
          ########################
          sql = select([req_model.c.month_date,
                      req_model.c.quarter,
                      req_model.c.num,            
                      req_model.c.system_id,
                      ],
                      and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                      ).order_by(desc(req_model.c.month_date))
          
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示

      #初始化header                
      sql_sys_rs = sql_sys.execute()
      #######################################################
      #header = []
      #header.append({'name':'月份'})
      #header.append({'name':'total','verbose_name':'合计', 'width':50})
      for row in sql_sys_rs:
          sys_id,name  = row
          i={}
          i['sys_id']=str(sys_id)
          i['name']=name
          #i['width']=70
          header.append(i)
              
      #print 'header',header
      #######################################################
      
      sql_line_rs = sql_line.execute()
      ###################################
      for row in sql_line_rs:
          #print 'row',row
          y,z = row
          y = str(y)
          #print 'y',y,'z',z
          data1[y] = {}
          data1[y]['month'] = z
          data1[y]['total'] = 0
          #print 'data[y]',data
          #print 'header.count()',header
          #print 'len',len(header)
          sql_sys_rs = sql_sys.execute()
          for row in sql_sys_rs:
              sys_id,name  = row
              sys_id = (sys_id)
              #print 'h',h
              data1[y][sys_id] = 0
              data1[y]['month_date'] = y
      ######################################
      #print 'header',header
      #print 'data',data
      #data_row[]   
      line_flag = 0
      title_num = 0
      ##############
      #total = 0
      ##############
      #header[title_num++] = u'月份’
      for row in sql_rs:
          #print row
          line_flag = 1
          month_date,rr,num,sys_id = row
          sys_id = str(sys_id)
          month_date = str(month_date)
          #print 'month_date',month_date
          #print 'num',num,'data[month_date][name]',data[month_date][name]
          data1[month_date][sys_id] = num
          data1[month_date]['num'] = num
          data1[month_date]['sys_id'] = str(sys_id)
          data1[month_date]['total'] += num
          #print data[month_date][name]
          #print data
      
      #print 'data',data
      data_view = list(sorted(data1.values()))
      

      #初始化总计字典
      data = {}
      data['label'] = header
      data['data'] = data_view
      data['name'] = u'准开发需求系统维度季度报表'
      response.template = 'charts_requirement_month_report_system_graph.xml'
      response.content_type = 'text/xml'
			
      result = {}    
      result['data'] = data      
      return result


#准开发需求月报表按照所处阶段维度展示
@expose('/charts/requirement/month/report/stage/graph')
def charts_requirement_month_report_stage_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_month_report_stage')               
      data1 = []
      header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.month_date,
                                req_model.c.month, 
                                func.sum(req_model.c.total), 
                                func.sum(req_model.c.new_ky),
                                func.sum(req_model.c.new_ss),
                                func.sum(req_model.c.new_wc),
                                func.sum(req_model.c.new_yc),
                                func.sum(req_model.c.new_zt),
                                func.sum(req_model.c.old_ky),
                                func.sum(req_model.c.old_ss),
                                func.sum(req_model.c.old_wc),
                                func.sum(req_model.c.old_yc),
                                func.sum(req_model.c.old_zt),
                               ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.kfzx == user_zx)
                          ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
      else:
          sql = select([req_model.c.month_date,
                        req_model.c.month, 
                        func.sum(req_model.c.total), 
                        func.sum(req_model.c.new_ky),
                        func.sum(req_model.c.new_ss),
                        func.sum(req_model.c.new_wc),
                        func.sum(req_model.c.new_yc),
                        func.sum(req_model.c.new_zt),
                        func.sum(req_model.c.old_ky),
                        func.sum(req_model.c.old_ss),
                        func.sum(req_model.c.old_wc),
                        func.sum(req_model.c.old_yc),
                        func.sum(req_model.c.old_zt),
                       ],
                  and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                  ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))

      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data1.append(row)

      data = {}
      data['name'] = u'准开发需求实施阶段维度季度报表'
      data['data'] = data1
      #data['total'] = total
      data['label'] = [[u'新建可研'],[u'新建实施'],[u'新建完成'],[u'新建异常'],[u'新建暂停'],[u'续建可研'],[u'续建实施'],[u'续建完成'],[u'续建异常'],[u'续建暂停']]
      response.template = 'charts_requirement_month_report_stage_graph.xml'
      response.content_type = 'text/xml'
    
      result = {}    
      result['data'] = data      
      return result


#准开发需求季度报表按照所处阶段维度展示
@expose('/charts/requirement/quarter/report/stage/graph')
def charts_requirement_quarter_report_stage_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('requirement_quarter_report_stage')               
      data1 = []
      header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
            sql = select([req_model.c.month_date,
                                req_model.c.quarter, 
                                func.sum(req_model.c.total), 
                                func.sum(req_model.c.new_ky),
                                func.sum(req_model.c.new_ss),
                                func.sum(req_model.c.new_wc),
                                func.sum(req_model.c.new_yc),
                                func.sum(req_model.c.new_zt),
                                func.sum(req_model.c.old_ky),
                                func.sum(req_model.c.old_ss),
                                func.sum(req_model.c.old_wc),
                                func.sum(req_model.c.old_yc),
                                func.sum(req_model.c.old_zt),
                               ],
                          and_( req_model.c.month_date >= begin_Date,
                                req_model.c.month_date <= end_Date,
                                req_model.c.kfzx == user_zx)
                          ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
      else:
          sql = select([req_model.c.month_date,
                        req_model.c.quarter, 
                        func.sum(req_model.c.total), 
                        func.sum(req_model.c.new_ky),
                        func.sum(req_model.c.new_ss),
                        func.sum(req_model.c.new_wc),
                        func.sum(req_model.c.new_yc),
                        func.sum(req_model.c.new_zt),
                        func.sum(req_model.c.old_ky),
                        func.sum(req_model.c.old_ss),
                        func.sum(req_model.c.old_wc),
                        func.sum(req_model.c.old_yc),
                        func.sum(req_model.c.old_zt),
                       ],
                  and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                  ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))

      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data1.append(row)

      data = {}
      data['name'] = u'准开发需求实施阶段维度季度报表'
      data['data'] = data1
      #data['total'] = total
      data['label'] = [[u'新建可研'],[u'新建实施'],[u'新建完成'],[u'新建异常'],[u'新建暂停'],[u'续建可研'],[u'续建实施'],[u'续建完成'],[u'续建异常'],[u'续建暂停']]
      response.template = 'charts_requirement_month_report_stage_graph.xml'
      response.content_type = 'text/xml'
    
      result = {}    
      result['data'] = data      
      return result

#项目任务书月报表按照开发中心维度图形展示
@expose('/charts/assignment/month/report/kfzx/graph')
def charts_assignment_month_report_kfzx_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_kfzx')               
      data1 = []
  #    header = []
      sql  = select([req_model.c.month_date,
                  req_model.c.type,
                  "''",
                  req_model.c.month,
                  req_model.c.new_num_01 + req_model.c.old_num_01 + req_model.c.new_num_02 + req_model.c.old_num_02 + req_model.c.new_num_03 + req_model.c.old_num_03 + req_model.c.new_num_04 + req_model.c.old_num_04 + req_model.c.new_num_05 + req_model.c.old_num_05 + req_model.c.new_num_06 + req_model.c.old_num_06 + req_model.c.new_num_07 + req_model.c.old_num_07 + req_model.c.new_num_08 + req_model.c.old_num_08,                    
                  req_model.c.new_num_01,
                  req_model.c.old_num_01,
                  req_model.c.new_num_02,
                  req_model.c.old_num_02,
                  req_model.c.new_num_03,
                  req_model.c.old_num_03,
                  req_model.c.new_num_04,
                  req_model.c.old_num_04,
                  req_model.c.new_num_05,
                  req_model.c.old_num_05,
                  req_model.c.new_num_06,
                  req_model.c.old_num_06,
                  req_model.c.new_num_07,
                  req_model.c.old_num_07,
                  req_model.c.new_num_08,
                  req_model.c.old_num_08,
                  ],
                  and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                  ).order_by(desc(req_model.c.month_date),asc(req_model.c.type))
          
      sql_rs = sql.execute()
      
      for row in sql_rs:
         row = list(row)
         if row[1] == '1':
            row[2] = u'项目'
         elif row[1] == '2':
            row[2] = u'子项目'
         elif row[1] == '3':
            row[2] = u'非项目任务'
         elif row[1] == '4':
            row[2] = u'专项采购任务'
        
         data1.append(row)
         print data1    
      data = {}
      data['name'] = u'任务书中心维度月报表'
      data['data'] = data1
      #data['total'] = total    
      data['label'] = [[u'北京开发中心新建'],[u'北京开发中心续建'],[u'上海开发中心新建'],[u'上海开发中心续建'],[u'厦门开发中心新建'],[u'厦门开发中心续建'],[u'广州开发中心新建'],[u'广州开发中心续建'],[u'成都开发中心新建'],[u'成都开发中心续建'],[u'武汉开发中心新建'],[u'武汉开发中心续建'],[u'深圳个贷支持中心新建'],[u'深圳个贷支持中心续建'],[u'香港支持中心新建'],[u'香港支持中心续建']]
      print data
      response.template = 'charts_assignment_month_report_kfzx_graph.xml'
      response.content_type = 'text/xml'
            
      result = {}    
      result['data'] = data      
      return result

#项目任务书季度报表按照开发中心维度图形展示
@expose('/charts/assignment/quarter/report/kfzx/graph')
def charts_assignment_quarter_report_kfzx_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_kfzx')               
      data1 = []
  #    header = []
      sql  = select([req_model.c.month_date,
                  req_model.c.type,
                  "''",
                  req_model.c.quarter,
                  req_model.c.new_num_01 + req_model.c.old_num_01 + req_model.c.new_num_02 + req_model.c.old_num_02 + req_model.c.new_num_03 + req_model.c.old_num_03 + req_model.c.new_num_04 + req_model.c.old_num_04 + req_model.c.new_num_05 + req_model.c.old_num_05 + req_model.c.new_num_06 + req_model.c.old_num_06 + req_model.c.new_num_07 + req_model.c.old_num_07 + req_model.c.new_num_08 + req_model.c.old_num_08,                    
                  req_model.c.new_num_01,
                  req_model.c.old_num_01,
                  req_model.c.new_num_02,
                  req_model.c.old_num_02,
                  req_model.c.new_num_03,
                  req_model.c.old_num_03,
                  req_model.c.new_num_04,
                  req_model.c.old_num_04,
                  req_model.c.new_num_05,
                  req_model.c.old_num_05,
                  req_model.c.new_num_06,
                  req_model.c.old_num_06,
                  req_model.c.new_num_07,
                  req_model.c.old_num_07,
                  req_model.c.new_num_08,
                  req_model.c.old_num_08,
                  ],
                  and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                  ).order_by(desc(req_model.c.month_date),asc(req_model.c.type))
          
      sql_rs = sql.execute()
      
      for row in sql_rs:
         row = list(row)
         if row[1] == '1':
            row[2] = u'项目'
         elif row[1] == '2':
            row[2] = u'子项目'
         elif row[1] == '3':
            row[2] = u'非项目任务'
         elif row[1] == '4':
            row[2] = u'专项采购任务'
         
         data1.append(row)
         #print data1    
      data = {}
      data['name'] = u'任务书中心维度季度报表'
      data['data'] = data1
      #data['total'] = total    
      data['label'] = [[u'北京开发中心新建'],[u'北京开发中心续建'],[u'上海开发中心新建'],[u'上海开发中心续建'],[u'厦门开发中心新建'],[u'厦门开发中心续建'],[u'广州开发中心新建'],[u'广州开发中心续建'],[u'成都开发中心新建'],[u'成都开发中心续建'],[u'武汉开发中心新建'],[u'武汉开发中心续建'],[u'深圳个贷支持中心新建'],[u'深圳个贷支持中心续建'],[u'香港支持中心新建'],[u'香港支持中心续建']]
      #print data
      response.template = 'charts_assignment_month_report_kfzx_graph.xml'
      response.content_type = 'text/xml'
            
      result = {}    
      result['data'] = data      
      return result

#项目任务书月报表按照项目阶段维度展示
@expose('/charts/assignment/month/report/stage/zxm/graph')
def charts_assignment_month_report_stage_zxm_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
        sql = select([req_model.c.month,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        req_model.c.month_date,
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                            req_model.c.month_date <= end_Date,
                            req_model.c.type == '2',
                            req_model.c.kfzx == user_zx)
                        ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
      else:                  
          sql = select([req_model.c.month,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        req_model.c.month_date,
                        ],
                        and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == '2')
                       ).order_by(desc(req_model.c.month_date))
                       
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      #line_flag = 0
      for row in sql_rs:
         row = list(row)
         #line_flag = 1
         data.append(row)
      response.template = 'charts_assignment_month_report_stage_graph.xml'
      response.content_type = 'text/xml'
      #data['begin_date'] = begin_Date
      #data['end_date'] = end_Date
      print data
      return {'data':data}

#项目任务书月报表按照项目阶段维度展示
@expose('/charts/assignment/quarter/report/stage/zxm/graph')
def charts_assignment_quarter_report_stage_zxm_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_stage')               
      data = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
         sql = select([req_model.c.quarter,
                       func.sum(req_model.c.total_num),
                       func.sum(req_model.c.ys_num_zc),
                       func.sum(req_model.c.ys_num_yc),
                       func.sum(req_model.c.cg_num_zc),
                       func.sum(req_model.c.cg_num_yc),
                       func.sum(req_model.c.ss_num_zc),
                       func.sum(req_model.c.ss_num_yc),
                       func.sum(req_model.c.tg_num_zc),
                       func.sum(req_model.c.tg_num_yc),
                       func.sum(req_model.c.yans_num_zc),
                       func.sum(req_model.c.yans_num_yc),
                       func.sum(req_model.c.js_num_zc),
                       func.sum(req_model.c.js_num_yc),
                       func.sum(req_model.c.wc_num_zc),
                       req_model.c.month_date,
                       ],
                       and_( req_model.c.month_date >= begin_Date,
                             req_model.c.month_date <= end_Date,
                             req_model.c.type == '2',
                             req_model.c.kfzx == user_zx)
                      ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
         
      else:        
          sql = select([req_model.c.quarter,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.ys_num_zc),
                        func.sum(req_model.c.ys_num_yc),
                        func.sum(req_model.c.cg_num_zc),
                        func.sum(req_model.c.cg_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.tg_num_zc),
                        func.sum(req_model.c.tg_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.js_num_zc),
                        func.sum(req_model.c.js_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        req_model.c.month_date,
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date,
                              req_model.c.type == '2')
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      #line_flag = 0
      for row in sql_rs:
         row = list(row)
         #line_flag = 1
         data.append(row)
      response.template = 'charts_assignment_month_report_stage_graph.xml'
      response.content_type = 'text/xml'
      #data['begin_date'] = begin_Date
      #data['end_date'] = end_Date
      print data
      return {'data':data}

#项目任务书月报表按照项目所处阶段维度展示非项目任务月度报表
@expose('/charts/assignment/month/report/stage/fxm/graph')
def charts_assignment_month_report_stage_fxm_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_stage_fxm')               
      data1 = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
         sql = select([req_model.c.month_date,
                       req_model.c.month,
                       func.sum(req_model.c.total_num),
                       func.sum(req_model.c.gys_num_zc),
                       func.sum(req_model.c.gys_num_yc),
                       func.sum(req_model.c.ss_num_zc),
                       func.sum(req_model.c.ss_num_yc),
                       func.sum(req_model.c.yans_num_zc),
                       func.sum(req_model.c.yans_num_yc),
                       func.sum(req_model.c.kffk_num_zc),
                       func.sum(req_model.c.kffk_num_yc),
                       func.sum(req_model.c.wc_num_zc),
                       ],
                       and_( req_model.c.month_date >= begin_Date,
                             req_model.c.month_date <= end_Date,
                             req_model.c.kfzx == user_zx)
                      ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                               
      else:        
          sql = select([req_model.c.month_date,
                        req_model.c.month,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.gys_num_zc),
                        func.sum(req_model.c.gys_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.kffk_num_zc),
                        func.sum(req_model.c.kffk_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date)
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                       

      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      for row in sql_rs:
         row = list(row)
         data1.append(row)
    
      data = {}
      data['name'] = u'任务书非项目阶段维度月报表'
      data['data'] = data1
      #data['total'] = total    
      data['label'] = [[u'供应商资源分配正常'],[u'供应商资源分配异常'],[u'实施正常'],[u'实施异常'],[u'验收正常'],[u'验收异常'],[u'开发任务付款正常'],[u'开发任务付款异常'],[u'完成']]
      #print data
      response.template = 'charts_assignment_month_report_stage_fxm_graph.xml'
      response.content_type = 'text/xml'
            
      result = {}    
      result['data'] = data      
      return result

    
#项目任务书季度报表按照项目所处阶段维度展示非项目任务月度报表
@expose('/charts/assignment/quarter/report/stage/fxm/graph')
def charts_assignment_quarter_report_stage_fxm_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_quarter_report_stage_fxm')               
      data1 = []
  #    header = []
      user_zx = request.user._kfzx_
      if request.user.department=='2':
         sql = select([req_model.c.month_date,
                       req_model.c.quarter,
                       func.sum(req_model.c.total_num),
                       func.sum(req_model.c.gys_num_zc),
                       func.sum(req_model.c.gys_num_yc),
                       func.sum(req_model.c.ss_num_zc),
                       func.sum(req_model.c.ss_num_yc),
                       func.sum(req_model.c.yans_num_zc),
                       func.sum(req_model.c.yans_num_yc),
                       func.sum(req_model.c.kffk_num_zc),
                       func.sum(req_model.c.kffk_num_yc),
                       func.sum(req_model.c.wc_num_zc),
                       ],
                       and_( req_model.c.month_date >= begin_Date,
                             req_model.c.month_date <= end_Date,
                             req_model.c.kfzx == user_zx)
                      ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
         
      else:        
          sql = select([req_model.c.month_date,
                        req_model.c.quarter,
                        func.sum(req_model.c.total_num),
                        func.sum(req_model.c.gys_num_zc),
                        func.sum(req_model.c.gys_num_yc),
                        func.sum(req_model.c.ss_num_zc),
                        func.sum(req_model.c.ss_num_yc),
                        func.sum(req_model.c.yans_num_zc),
                        func.sum(req_model.c.yans_num_yc),
                        func.sum(req_model.c.kffk_num_zc),
                        func.sum(req_model.c.kffk_num_yc),
                        func.sum(req_model.c.wc_num_zc),
                        ],
                        and_( req_model.c.month_date >= begin_Date,
                              req_model.c.month_date <= end_Date)
                       ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                   
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data1.append(row)

      data = {}
      data['name'] = u'任务书非项目阶段维度季度报表'
      data['data'] = data1
      #data['total'] = total    
      data['label'] = [[u'供应商资源分配正常'],[u'供应商资源分配异常'],[u'实施正常'],[u'实施异常'],[u'验收正常'],[u'验收异常'],[u'开发任务付款正常'],[u'开发任务付款异常'],[u'完成']]
      #print data
      response.template = 'charts_assignment_month_report_stage_fxm_graph.xml'
      response.content_type = 'text/xml'
              
      result = {}    
      result['data'] = data      
      return result

#版本月报表按照版本所处阶段维度图形展示
@expose('/charts/version/month/report/stage/graph')
def charts_version_month_report_stage_graph():
    from uliweb.orm import get_model
#    from uliweb.utils.generic import ListView
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
#    from views import *
    
    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    req_model = get_model('version_month_report_stage')               
    data1 = []
#    header = []
    
    sql = select([req_model.c.month_date,
                  req_model.c.month,
                  req_model.c.total_num,
                  req_model.c.version_num_00,
                  req_model.c.version_num_02,
                  req_model.c.version_num_03,
                  req_model.c.version_num_04,
                  req_model.c.version_num_05,
                  req_model.c.version_num_06,
                  req_model.c.version_num_07,
                  req_model.c.version_num_08,
                  req_model.c.version_num_09,
                  req_model.c.version_num_10,
                  req_model.c.version_num_11,
                  req_model.c.version_num_FN],
                 and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                 ).order_by(desc(req_model.c.month_date))
                
    
    sql_rs = sql.execute()
    
    ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
    line_flag = 0
    for row in sql_rs:
        row = list(row)
        line_flag = 1
        data1.append(row)

    data = {}
    data['name'] = u'版本按照所处阶段统计月报表'
    data['data'] = data1
    #data['total'] = total    
    data['label'] = [[u'未开始'],[u'需求分析'],[u'概要设计'],[u'详细设计'],[u'开发'],[u'内部测试'],[u'功能集成测试'],[u'非功能集成测试'],[u'用户测试'],[u'版本验证测试'],[u'版本投产'],[u'完成']]
    #print data
    response.template = 'charts_version_month_report_stage_graph.xml'
    response.content_type = 'text/xml'
            
    result = {}    
    result['data'] = data      
    return result

#版本季度报表按照版本所处阶段维度图形展示
@expose('/charts/version/quarter/report/stage/graph')
def charts_version_quarter_report_stage_graph():
    from uliweb.orm import get_model
#    from uliweb.utils.generic import ListView
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
#    from views import *
    
    pageno = int(request.GET.get('pageno', 0))    
    begin_Date,end_Date = get_date()
    req_model = get_model('version_quarter_report_stage')               
    data1 = []
#    header = []
    
    sql = select([req_model.c.month_date,
                    req_model.c.quarter,
                    req_model.c.total_num,
                    req_model.c.version_num_00,
                    req_model.c.version_num_02,
                    req_model.c.version_num_03,
                    req_model.c.version_num_04,
                    req_model.c.version_num_05,
                    req_model.c.version_num_06,
                    req_model.c.version_num_07,
                    req_model.c.version_num_08,
                    req_model.c.version_num_09,
                    req_model.c.version_num_10,
                    req_model.c.version_num_11,
                    req_model.c.version_num_FN],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                    ).order_by(desc(req_model.c.month_date))
                
    
    sql_rs = sql.execute()
    
    ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
    line_flag = 0
    for row in sql_rs:
        row = list(row)
        line_flag = 1
        data1.append(row)

    data = {}
    data['name'] = u'版本按照所处阶段统计季度报表'
    data['data'] = data1
    #data['total'] = total    
    data['label'] = [[u'未开始'],[u'需求分析'],[u'概要设计'],[u'详细设计'],[u'开发'],[u'内部测试'],[u'功能集成测试'],[u'非功能集成测试'],[u'用户测试'],[u'版本验证测试'],[u'版本投产'],[u'完成']]
    #print data
    response.template = 'charts_version_month_report_stage_graph.xml'
    response.content_type = 'text/xml'
            
    result = {}    
    result['data'] = data      
    return result


#定义一个供项目、子项目、非项目异常数据获取函数
def get_assignment_month_report_kfzx_xmyc_graph(type):
    from uliweb.orm import get_model
#    from uliweb.utils.generic import ListView
    from uliweb.utils.generic import SimpleListView
    from uliweb import request
#    from views import *
    
    begin_Date,end_Date = get_date()
    req_model = get_model('assignment_month_report_kfzx_xmyc')               
    data1 = []
#    header = []
    sql = select([req_model.c.month_date,
                    req_model.c.month,
                    req_model.c.yc_num_01+req_model.c.yczz_num_01+req_model.c.zt_num_01+req_model.c.yc_num_02+req_model.c.yczz_num_02+req_model.c.zt_num_02+req_model.c.yc_num_03+req_model.c.yczz_num_03+req_model.c.zt_num_03+req_model.c.yc_num_04+req_model.c.yczz_num_04+req_model.c.zt_num_04+req_model.c.yc_num_05+req_model.c.yczz_num_05+req_model.c.zt_num_05+req_model.c.yc_num_06+req_model.c.yczz_num_06+req_model.c.zt_num_06+req_model.c.yc_num_07+req_model.c.yczz_num_07+req_model.c.zt_num_07+req_model.c.yc_num_08+req_model.c.yczz_num_08+req_model.c.zt_num_08,      
                    req_model.c.yc_num_01,  
                    req_model.c.yczz_num_01,
                    req_model.c.zt_num_01,  
                    req_model.c.yc_num_02,  
                    req_model.c.yczz_num_02,
                    req_model.c.zt_num_02,  
                    req_model.c.yc_num_03,  
                    req_model.c.yczz_num_03,
                    req_model.c.zt_num_03,  
                    req_model.c.yc_num_04,  
                    req_model.c.yczz_num_04,
                    req_model.c.zt_num_04,  
                    req_model.c.yc_num_05,  
                    req_model.c.yczz_num_05,
                    req_model.c.zt_num_05,  
                    req_model.c.yc_num_06,  
                    req_model.c.yczz_num_06,
                    req_model.c.zt_num_06,  
                    req_model.c.yc_num_07,  
                    req_model.c.yczz_num_07,
                    req_model.c.zt_num_07,  
                    req_model.c.yc_num_08,  
                    req_model.c.yczz_num_08,
                    req_model.c.zt_num_08,
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == type)
                    ).order_by(desc(req_model.c.month_date))
                    
    
    sql_rs = sql.execute()
    ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
    line_flag = 0
    for row in sql_rs:
        row = list(row)
        line_flag = 1
        data1.append(row)
    
    
    data = {}
    
    data['data'] = data1
    #data['total'] = total    
    data['label'] = [[u'北京开发中心延迟'],[u'北京开发中心异常终止'],[u'北京开发中心暂停'],[u'上海开发中心延迟'],[u'上海开发中心异常终止'],[u'上海开发中心暂停'],[u'厦门开发中心延迟'],[u'厦门开发中心异常终止'],[u'厦门开发中心暂停'],[u'广州开发中心延迟'],[u'广州开发中心异常终止'],[u'广州开发中心暂停'],[u'成都开发中心延迟'],[u'成都开发中心异常终止'],[u'成都开发中心暂停'],[u'武汉开发中心延迟'],[u'武汉开发中心异常终止'],[u'武汉开发中心暂停'],[u'深圳个贷支持中心延迟'],[u'深圳个贷支持中心异常终止'],[u'深圳个贷支持中心暂停'],[u'香港支持中心延迟'],[u'香港支持中心异常终止'],[u'香港支持中心暂停']]    #print data
    response.template = 'charts_assignment_month_report_kfzx_xmyc_graph.xml'
    response.content_type = 'text/xml'
    return data


#项目任务书月报表按照开发中心、项目异常维度展示
@expose('/charts/assignment/month/report/kfzx/xmyc/graph')
def charts_assignment_month_report_kfzx_xmyc_graph():
    type = '1'
    data = get_assignment_month_report_kfzx_xmyc_graph(type)
    data['name'] = u'项目任务书项目异常月报表'       
    result = {}    
    result['data'] = data      
    return result

#项目任务书月报表按照开发中心、子项目异常维度展示
@expose('/charts/assignment/month/report/kfzx/xmyc/zxm/graph')
def charts_assignment_month_report_kfzx_xmyc_zxm_graph():
    type = '2'
    data = get_assignment_month_report_kfzx_xmyc_graph(type)
    data['name'] = u'项目任务书子项目异常月报表'       
    result = {}    
    result['data'] = data      
    return result

#项目任务书月报表按照开发中心、子项目异常维度展示
@expose('/charts/assignment/month/report/kfzx/xmyc/fxm/graph')
def charts_assignment_month_report_kfzx_xmyc_fxm_graph():
    type = '3'
    data = get_assignment_month_report_kfzx_xmyc_graph(type)
    data['name'] = u'项目任务书非项目异常月报表'       
    result = {}    
    result['data'] = data      
    return result
    
#定义一个供项目、子项目、非项目异常原因分析数据获取函数    
def get_assignment_month_report_xmycfx_graph(type):
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
         
      begin_Date,end_Date = get_date()
      req_model = get_model('assignment_month_report_xmycfx')               
      data1 = []
  #    header = []
      sql = select([req_model.c.month_date,
                    req_model.c.month,
                    func.sum(req_model.c.delay_num_01)+func.sum(req_model.c.delay_num_02)+func.sum(req_model.c.delay_num_03)+func.sum(req_model.c.delay_num_04)+func.sum(req_model.c.delay_num_05)+func.sum(req_model.c.delay_num_06)+func.sum(req_model.c.delay_num_09),      
                    func.sum(req_model.c.delay_num_01),
                    func.sum(req_model.c.delay_num_02),
                    func.sum(req_model.c.delay_num_03),
                    func.sum(req_model.c.delay_num_04),
                    func.sum(req_model.c.delay_num_05),
                    func.sum(req_model.c.delay_num_06),
                    func.sum(req_model.c.delay_num_09),
                    ],
                    and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date,req_model.c.type == type)
                   ).group_by(req_model.c.month_date).order_by(desc(req_model.c.month_date))
                   
      
      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示

      for row in sql_rs:
         row = list(row)
         data1.append(row)
      data = {}
      data['data'] = data1
      #data['total'] = total    
      data['label'] = [[u'预算延迟'],[u'采购延迟'],[u'技术复杂度超预期'],[u'需求不锁定'],[u'验收延迟'],[u'决算延迟'],[u'其他']]
      response.template = 'charts_assignment_month_report_xmycfx_graph.xml'
      response.content_type = 'text/xml'
      return data   


#项目任务书月报表按照开发中心、项目异常维度展示
@expose('/charts/assignment/month/report/xmycfx/graph')
def charts_assignment_month_report_xmycfx_graph():
      type = '1'
      data = get_assignment_month_report_xmycfx_graph(type)
      data['name'] = u'项目任务书项目异常原因分析月报表'       
      result = {}    
      result['data'] = data      
      return result
      
#项目任务书月报表按照开发中心、子项目异常维度展示
@expose('/charts/assignment/month/report/xmycfx/zxm/graph')
def charts_assignment_month_report_xmycfx_zxm_graph():
      type = '2'
      data = get_assignment_month_report_xmycfx_graph(type)
      data['name'] = u'项目任务书子项目异常原因分析月报表'       
      result = {}    
      result['data'] = data      
      return result

#项目任务书月报表按照开发中心、非项目异常维度展示
@expose('/charts/assignment/month/report/xmycfx/fxm/graph')
def charts_assignment_month_report_xmycfx_fxm_graph():
      type = '3'
      data = get_assignment_month_report_xmycfx_graph(type)
      data['name'] = u'项目任务书非项目异常原因分析月报表'       
      result = {}    
      result['data'] = data      
      return result


#版本月报表按照开发中心维度展现版本异常情况   
@expose('/charts/version/month/report/kfzx/bbyc/graph')
def charts_version_month_report_kfzx_bbyc_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
      
      pageno = int(request.GET.get('pageno', 0))    
      begin_Date,end_Date = get_date()
      req_model = get_model('version_month_report_kfzx_bbyc')               
      data1 = []
  #    header = []
      sql = select([req_model.c.month_date,
                  req_model.c.month,
                  req_model.c.yc_num_01+req_model.c.yczz_num_01+req_model.c.zt_num_01+req_model.c.yc_num_02+req_model.c.yczz_num_02+req_model.c.zt_num_02+req_model.c.yc_num_03+req_model.c.yczz_num_03+req_model.c.zt_num_03+req_model.c.yc_num_04+req_model.c.yczz_num_04+req_model.c.zt_num_04+req_model.c.yc_num_05+req_model.c.yczz_num_05+req_model.c.zt_num_05+req_model.c.yc_num_06+req_model.c.yczz_num_06+req_model.c.zt_num_06+req_model.c.yc_num_07+req_model.c.yczz_num_07+req_model.c.zt_num_07+req_model.c.yc_num_08+req_model.c.yczz_num_08+req_model.c.zt_num_08,                  
                  req_model.c.yc_num_01,
                  req_model.c.yczz_num_01,
                  req_model.c.zt_num_01,
                  req_model.c.yc_num_02,
                  req_model.c.yczz_num_02,
                  req_model.c.zt_num_02,
                  req_model.c.yc_num_03,
                  req_model.c.yczz_num_03,
                  req_model.c.zt_num_03,
                  req_model.c.yc_num_04,
                  req_model.c.yczz_num_04,
                  req_model.c.zt_num_04,
                  req_model.c.yc_num_05,
                  req_model.c.yczz_num_05,
                  req_model.c.zt_num_05,
                  req_model.c.yc_num_06,
                  req_model.c.yczz_num_06,
                  req_model.c.zt_num_06,
                  req_model.c.yc_num_07,
                  req_model.c.yczz_num_07,
                  req_model.c.zt_num_07,
                  req_model.c.yc_num_08,
                  req_model.c.yczz_num_08,
                  req_model.c.zt_num_08,
                  ],
                 and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                 ).order_by(desc(req_model.c.month_date))


      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示
      line_flag = 0
      for row in sql_rs:
         row = list(row)
         line_flag = 1
         data1.append(row)
      
      data = {}
      data['name'] = u'版本异常分析月报表'
      data['data'] = data1
      #data['total'] = total    
      data['label'] = [[u'北京开发中心延迟'],[u'北京开发中心异常终止'],[u'北京开发中心暂停'],[u'上海开发中心延迟'],[u'上海开发中心异常终止'],[u'上海开发中心暂停'],[u'厦门开发中心延迟'],[u'厦门开发中心异常终止'],[u'厦门开发中心暂停'],[u'广州开发中心延迟'],[u'广州开发中心异常终止'],[u'广州开发中心暂停'],[u'成都开发中心延迟'],[u'成都开发中心异常终止'],[u'成都开发中心暂停'],[u'武汉开发中心延迟'],[u'武汉开发中心异常终止'],[u'武汉开发中心暂停'],[u'深圳个贷支持中心延迟'],[u'深圳个贷支持中心异常终止'],[u'深圳个贷支持中心暂停'],[u'香港支持中心延迟'],[u'香港支持中心异常终止'],[u'香港支持中心暂停']]    #print data
      #print data
      response.template = 'charts_version_month_report_kfzx_bbyc_graph.xml'
      response.content_type = 'text/xml'
              
      result = {}    
      result['data'] = data      
      return result

#版本月报表按照开发中心维度展现版本异常情况 
@expose('/charts/version/month/report/bbycfx/graph')
def charts_version_month_report_bbycfx_graph():
      from uliweb.orm import get_model
  #    from uliweb.utils.generic import ListView
      from uliweb.utils.generic import SimpleListView
      from uliweb import request
  #    from views import *
         
      begin_Date,end_Date = get_date()
      req_model = get_model('version_month_report_bbycfx')               
      data1 = []
  #    header = []
      sql = select([req_model.c.month_date,
                  req_model.c.month,
                  func.sum(req_model.c.delay_num_01)+func.sum(req_model.c.delay_num_02)+func.sum(req_model.c.delay_num_03)+func.sum(req_model.c.delay_num_04)+func.sum(req_model.c.delay_num_05)+func.sum(req_model.c.delay_num_06)+func.sum(req_model.c.delay_num_09),                        
                  func.sum(req_model.c.delay_num_01),
                  func.sum(req_model.c.delay_num_02),
                  func.sum(req_model.c.delay_num_03),
                  func.sum(req_model.c.delay_num_04),
                  func.sum(req_model.c.delay_num_05),
                  func.sum(req_model.c.delay_num_06),
                  func.sum(req_model.c.delay_num_09),
                  ],
                 and_( req_model.c.month_date >= begin_Date,req_model.c.month_date <= end_Date)
                 ).group_by(req_model.c.month).order_by(desc(req_model.c.month_date))


      sql_rs = sql.execute()
      ##line_flag标记本查询是否有记录,line_flag等于0则意味着本次查询无数据，总计则不显示

      for row in sql_rs:
         row = list(row)
         data1.append(row)

      
      data = {}
      data['name'] = u'版本异常原因分析月报表'
      data['data'] = data1
      #data['total'] = total    
      data['label'] = [[u'预算延迟'],[u'采购延迟'],[u'技术复杂度超预期'],[u'需求不锁定'],[u'验收延迟'],[u'决算延迟'],[u'其他']]
      #print data
      response.template = 'charts_version_month_report_bbycfx_graph.xml'
      response.content_type = 'text/xml'
              
      result = {}    
      result['data'] = data      
      return result



 

        
     
